from datetime import datetime
from typing import Optional, List, Dict, Any
from sqlalchemy import Column, Integer, String, ForeignKey, TIMESTAMP, Text, func
from sqlalchemy.dialects.postgresql import JSONB, ARRAY
from sqlalchemy.orm import relationship

from app.database import Base

class School(Base):
    """学校模型"""
    __tablename__ = "schools"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), unique=True, nullable=False)
    created_at = Column(TIMESTAMP, default=datetime.utcnow)
    updated_at = Column(TIMESTAMP, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关系
    classes = relationship("Class", back_populates="school")


class Class(Base):
    """班级模型"""
    __tablename__ = "classes"

    id = Column(Integer, primary_key=True, index=True)
    school_id = Column(Integer, ForeignKey("schools.id"), nullable=False)
    class_code = Column(String(20), unique=True, nullable=False, index=True)
    class_name = Column(String(100), nullable=False, index=True)
    start_date = Column(String, nullable=False, default='2025-09-01')
    college_name = Column(String(100), nullable=False, index=True)
    grade_name = Column(String(10), nullable=False, index=True)
    major_code = Column(String(20), nullable=False)
    major_name = Column(String(100), nullable=False, index=True)
    major_direction = Column(String(100), index=True)
    education_level = Column(String(20), nullable=False, index=True)
    student_count = Column(String(20), default="0")
    academic_year_code = Column(String(10), nullable=False)
    academic_year_name = Column(String(20), nullable=False, index=True)
    term_code = Column(String(10), nullable=False, index=True)
    campus_id = Column(String(10), nullable=False)
    campus_name = Column(String(50), nullable=False, index=True)
    created_at = Column(TIMESTAMP, default=datetime.utcnow)
    updated_at = Column(TIMESTAMP, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关系
    school = relationship("School", back_populates="classes")
    courses = relationship("Course", back_populates="class_")


class Course(Base):
    """课程模型"""
    __tablename__ = "courses"

    id = Column(Integer, primary_key=True, index=True)
    class_id = Column(Integer, ForeignKey("classes.id"), nullable=False)
    class_code = Column(String(100), nullable=False)
    course_name = Column(String(255), nullable=False)
    course_name_abbr = Column(String(100))
    teacher = Column(JSONB)
    day_of_week = Column(Integer, nullable=False)
    dateDigitSeparator = Column(String(100), nullable=False)
    campus = Column(String(100), nullable=False)
    location = Column(JSONB, nullable=False)
    section = Column(JSONB, nullable=False)
    weeks = Column(JSONB, nullable=False)
    className = Column(String(100), nullable=False)
    all_classes = Column(ARRAY(Text), nullable=False)
    courseType = Column(String(50))
    credit = Column(String(50))
    classHour = Column(String(50))
    studentCount = Column(String(50), default="0")
    assessment = Column(String(50))
    teachingMethod = Column(String(50))
    bremark = Column(JSONB)
    created_at = Column(TIMESTAMP, default=datetime.utcnow)
    updated_at = Column(TIMESTAMP, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关系
    class_ = relationship("Class", foreign_keys=[class_id], back_populates="courses")


class User(Base):
    """用户模型"""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    role = Column(String(20), nullable=False, default="admin")
    created_at = Column(TIMESTAMP, default=datetime.utcnow)
    updated_at = Column(TIMESTAMP, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关系
    logs = relationship("SystemLog", back_populates="user")


class SystemLog(Base):
    """系统日志模型"""
    __tablename__ = "system_logs"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    log_type = Column(String(20), nullable=False)
    action = Column(String(50), nullable=False)
    description = Column(Text)
    table_name = Column(String(50))
    record_id = Column(Integer)
    old_value = Column(JSONB)
    new_value = Column(JSONB)
    ip_address = Column(String(50))
    user_agent = Column(String(255))
    status_code = Column(Integer)
    request_data = Column(JSONB)
    response_data = Column(JSONB)
    created_at = Column(TIMESTAMP, default=datetime.utcnow)

    # 关系
    user = relationship("User", back_populates="logs")
